package cn.java86.dao;

import cn.java86.util.DBUtil;
import cn.java86.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 * 默认是mysql
 */
public class StudentDaoImpl implements StudentDao {
    @Override
    public int add(Student s) {
        Connection conn = DBUtil.get();
        //待补充
        String sql = "insert into student(sname,sex,birthday,gradeid) values(?,?,?,?)";
        PreparedStatement statement = null;
        try {
            statement = conn.prepareStatement(sql);
            statement.setString(1, s.getSname());
            statement.setString(2, s.getSex());
            java.sql.Date birthday = new java.sql.Date(s.getBirthday().getTime());
            statement.setDate(3, birthday);
            statement.setLong(4, s.getGradeid());
            int i = statement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(statement, conn);
        }
        return 0;
    }

    @Override
    public int update(Student s) {
        return 0;
    }

    @Override
    public int del(long stuno) {
        Connection conn = DBUtil.get();
        //待补充
        String sql = "delete from  s_student where stuno=?";
        PreparedStatement statement = null;
        try {
            statement = conn.prepareStatement(sql);
            statement.setLong(1, stuno);
            int i = statement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(statement, conn);
        }
        return 0;
    }

    @Override
    public Student get(long stuno) {
        // * 不好
        String sql = "select * from s_student where stuno=?";
        Connection conn = DBUtil.get();
        PreparedStatement ps = null;
        ResultSet rs = null;
        //待补充
        try {
            ps = conn.prepareStatement(sql);
            ps.setLong(1, stuno);
            rs = ps.executeQuery();
            if (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setSname(rs.getString("sname"));
                student.setSex(rs.getString("sex"));
                student.setQq(rs.getString("qq"));
                student.setTelephone(rs.getString("telephone"));
                student.setBirthday(rs.getDate("birthday"));
                student.setEnterdate(rs.getDate("enterdate"));
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return null;
    }

    //模糊搜索，找不到返回空集合
    @Override
    public List<Student> search(String sname, String qq) {
        String sql = "select * from s_student where sname like ? and qq like ?";
        Connection conn = DBUtil.get();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + sname + "%");
            ps.setString(2, "%" + qq + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getLong("stuno"));
                student.setGradeid(rs.getLong("gradeid"));
                student.setSname(rs.getString("sname"));
                student.setSex(rs.getString("sex"));
                student.setQq(rs.getString("qq"));
                student.setTelephone(rs.getString("telephone"));
                student.setBirthday(rs.getDate("birthday"));
                student.setEnterdate(rs.getDate("enterdate"));
                //
                list.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return list;
    }


}
